﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_FinanceMonthTable_Select]
	(
		@UserId NCHAR(10),
		@StartDate NCHAR(10),
		@EndDate NCHAR(10)	
	)
AS
BEGIN
	
	DECLARE @cid VARCHAR(8000)
	SELECT @cid=au.Cid FROM aspnet_Users au where au.username =@UserId
	IF @cid='[ALL]'
	BEGIN
		  SELECT 
 
  	@cid=
  	REPLACE(REPLACE(( SELECT id FROM Company  
                                        order by id asc FOR XML AUTO ), '<Company id="', '['), '"/>', ']')
	END
	ELSE IF @cid=''
	BEGIN
		SELECT @cid= '['+Convert(varchar,au.CompanyId)+']'
		  FROM aspnet_Users au where au.username =@UserId
	END
	
	;WITH list AS (
SELECT 
	  maid,
      MAname
      ,sum(bd) AS bd
      ,sum(wz)AS wz
      ,sum(qt)+sum(tz)AS qt
      ,sum(xfml)AS xfml
      ,sum(xsml)AS xsml
      ,sum(xse)AS xse
      ,sum(mlhj)AS mlhj
      ,sum(xzys)AS xzys
      ,sum(wksr)AS wksr
      ,sum(tx)AS tx
      ,sum(rcfy)AS rcfy
      ,sum(cl)AS cl
      ,sum(sscl)AS sscl
      ,sum(zxf+gdzc)AS zxf
      ,sum(albb) AS albb,
      sum(qyqq) AS qyqq
      
 FROM FinanceMonthTable WHERE 
 tmonth BETWEEN @StartDate AND @EndDate and 
 
 --MAid IN  (SELECT c.Mid FROM Company c WHERE CHARINDEX('['+CONVERT(VARCHAR, c.Id)+']' , 
 --                       (SELECT au.Cid FROM aspnet_Users au where au.username =@UserId),0)>0) 
 CHARINDEX('['+CONVERT(VARCHAR, CompanyId)+']' , 
                       --(SELECT au.Cid FROM aspnet_Users au where au.username =@UserId)
                      @cid ,0)>0
 
GROUP BY maid,MAname
 )

SELECT * FROM list,(SELECT SUM(bd) AS bd1,
      SUM([wz]) AS wz1,
      SUM([qt]) AS qt1,
      SUM([xfml]) AS xfml1,
      SUM([xsml]) AS xsml1,
      SUM([xse]) AS xse1,
      SUM([mlhj]) AS mlhj1,
      SUM([xzys]) AS xzys1,
      SUM([wksr]) AS wksr1,
      SUM([tx]) as tx1,
      SUM([rcfy]) AS rcfy1,
      SUM([cl]) AS cl1,
      SUM([sscl]) AS sscl1,
      SUM([zxf]) as zxf1 
      ,sum(albb) AS albb1,
      sum(qyqq) AS qyqq1
                    FROM list ) AS n
      
       ORDER BY maid
END
